package action;

import java.io.File;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import javax.activation.DataHandler;
import javax.activation.DataSource;
import javax.activation.FileDataSource;
import javax.annotation.Resource;
import javax.mail.BodyPart;
import javax.mail.Message;
import javax.mail.Multipart;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeBodyPart;
import javax.mail.internet.MimeMessage;
import javax.mail.internet.MimeMultipart;
import javax.mail.internet.MimeUtility;

import org.apache.struts2.ServletActionContext;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Controller;

import bean.Course;
import bean.Course_Student;
import bean.SingleCourse;
import bean.Student;
import bean.Teacher;
import entity.StudentCount;
import jxl.CellView;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import net.sf.json.JsonConfig;
import service.FileService;
import service.LoginService;

@Controller("FileManage")
@Scope("prototype")
public class FileManageAction {
	@Resource FileService fileService;
	@Resource LoginService loginService;
	
	private File upfile;
	private String upfileFileName ;  // 上传文件的名字 ,FileName 固定的写法  
	private String upfileContentType ; //上传文件的类型， ContentType 固定的写法  
	private int tid;
	private String studentCountArray;
	private String studentCountJson;
	private Map<String, Object> exportJson;		//回传的数据

	//--------------------------get和set方法------------------------	
	
	public String getStudentCountJson() {
		return studentCountJson;
	}

	public void setStudentCountJson(String studentCountJson) {
		this.studentCountJson = studentCountJson;
	}
	
	public String getStudentCountArray() {
		return studentCountArray;
	}

	public void setStudentCountArray(String studentCountArray) {
		this.studentCountArray = studentCountArray;
	}
	
	public Map<String, Object> getExportJson() {
		return exportJson;
	}

	public void setExportJson(Map<String, Object> exportJson) {
		this.exportJson = exportJson;
	}

	public File getUpfile() {
		return upfile;
	}

	public int getTid() {
		return tid;
	}

	public void setTid(int tid) {
		this.tid = tid;
	}

	public void setUpfile(File upfile) {
		this.upfile = upfile;
	}
	
	public String getUpfileFileName() {
		return upfileFileName;
	}

	public void setUpfileFileName(String upfileFileName) {
		this.upfileFileName = upfileFileName;
	}

	public String getUpfileContentType() {
		return upfileContentType;
	}

	public void setUpfileContentType(String upfileContentType) {
		this.upfileContentType = upfileContentType;
	}

	//====================================Action=========================================
	/**
	 * 跳转到上传文件主页
	 * @return uploadindex
	 */
	public String uploadIndex()
	{
        ServletActionContext.getRequest().getSession().removeAttribute("uploadresult");
		return "uploadindex";
	}
	
	/**
	 * 将上传的文件存入数据库
	 * @return savefinish
	 */
	public String uploadfile()
	{
		final int tid = getTid();
		try 
	    {
	        Workbook rwb=Workbook.getWorkbook(getUpfile());	//解析excel
	        Sheet sheet=rwb.getSheet("student");		//获取学生信息页的内容
	        int rows = sheet.getRows();					//得到所有的行
	        List<Student> studentlist = new ArrayList<>();
	        for (int i = 2; i < rows; i++) 		//从第2行第0列开始为有效数据（第0行为大标题，第1行为小标题，之后是数据）
	        {
	        	//getCell（列数，行数）
	        	Student student = new Student();
	        	student.setSno(sheet.getCell(0, i).getContents());		//学号
                student.setSname(sheet.getCell(1, i).getContents());	//学生姓名
                student.setCollege(sheet.getCell(2, i).getContents());	//学院
                student.setSmajor(sheet.getCell(3, i).getContents());	//专业
                student.setSsex(sheet.getCell(4, i).getContents());		//性别
                student.setSclass(sheet.getCell(5, i).getContents());	//班级
                student.setSphone(sheet.getCell(6, i).getContents());	//联系电话
                student.setSemail(sheet.getCell(7, i).getContents());	//电子邮箱
                student.setTeacher_id(tid);								//教师id，下同
                studentlist.add(student);
	        }
	        
	        sheet=rwb.getSheet("course");		//获取课程信息页的内容
			rows = sheet.getRows();		
			List<Course> courselist = new ArrayList<>();
			for (int i = 2; i < rows; i++) 		
	        {
	        	//getCell（列数，行数）
	        	Course course = new Course();
	        	course.setCname(sheet.getCell(0, i).getContents());						//课程名称
	        	course.setCplace(sheet.getCell(1, i).getContents());					//上课地点
	        	course.setCday(Integer.parseInt(sheet.getCell(2, i).getContents()));	//周几上课
	        	course.setCstart(Integer.parseInt(sheet.getCell(3, i).getContents()));	//开始节数
	        	course.setCend(Integer.parseInt(sheet.getCell(4, i).getContents()));	//结束节数
	        	course.setCno(sheet.getCell(5, i).getContents());						//课程编号
	        	course.setTeacher_id(tid);
                courselist.add(course);
	        }
			
			fileService.addStudentsAndCourses(studentlist, courselist);
			sheet = rwb.getSheet("course_student");									//获取学生-课程信息页的内容
			rows = sheet.getRows();		
			List<Course_Student> course_Studentlist = new ArrayList<>();
			for (int i = 2; i < rows; i++) 		
	        {
				Course_Student course_Student = new Course_Student();
				course_Student.setCid(fileService.getCidByCno(sheet.getCell(0, i).getContents()));			//课程编号
				course_Student.setSid(fileService.getSidBySno(sheet.getCell(1, i).getContents()));			//学号	
				course_Student.setTeacher_id(tid);
	        	course_Studentlist.add(course_Student);
	        }
			
			sheet=rwb.getSheet("singlecourse");					//获取课程信息页的内容
			rows = sheet.getRows();		
			List<SingleCourse> singleCourselist = new ArrayList<>();
			for (int i = 2; i < rows; i++) 		
	        {
				SingleCourse singleCourse = new SingleCourse();
				singleCourse.setCid(fileService.getCidByCno(sheet.getCell(0, i).getContents()));	//课程编号
				System.out.println(singleCourse.getCid());
				singleCourse.setHaspoint(0);														//默认未点名
				singleCourse.setWeek(Integer.parseInt(sheet.getCell(1, i).getContents()));			//第几周
				singleCourse.setContent(sheet.getCell(2, i).getContents());							//课程内容安排
				singleCourse.setTeacher_id(tid);
				singleCourselist.add(singleCourse);
	        }
			fileService.addStu_couAndSingleCourseAndCourseBehave(tid, course_Studentlist, singleCourselist);
	        ServletActionContext.getRequest().getSession().setAttribute("uploadresult", "success");
	    }
		catch (Exception e) 
	    {
	        e.printStackTrace();
	        ServletActionContext.getRequest().getSession().setAttribute("uploadresult", "fail");
	    }
		return "uploadfinish";
	}

	/**
	 * 发送汇总单节课所有学生信息到指定邮箱
	 * @return exportfinish
	 */
	@SuppressWarnings({ "unchecked", "static-access" })
	public String adexportAllStudentInfo()
	{
		Map<String, Object> map = new HashMap<String, Object>();
		Teacher teacher = loginService.findTeacherById(tid);
		if(teacher != null)
		{
			if(teacher.getTemail().equals(""))
			{
				map.put("result", "noemail");
				this.setExportJson(map);
				return "exportfinish";
			}
		}
		String nowtime = new SimpleDateFormat("yyyy年MM月dd日 HH点mm分ss秒").format(new Date());		//格式化时间
		File file = null;
		try {
			JSONArray jsonArray = new JSONArray();
			//创建需要储存的list
			List<StudentCount> studentCountList = new ArrayList<>();

			if(!getStudentCountArray().isEmpty())
			{
				jsonArray = JSONArray.fromObject(getStudentCountArray());
				studentCountList = jsonArray.toList(jsonArray, new StudentCount(), new JsonConfig());				
				//将信息存入excel
		        WritableWorkbook workbook;
		        file = new File("课程信息汇总_" + studentCountList.get(0).getCname()  + "_" + nowtime + ".xls");
				workbook = Workbook.createWorkbook(file);
				//生成名为“信息汇总”的工作表，参数0表示这是第一页   
		        WritableSheet sheet = workbook.createSheet("信息汇总", 0); 
		        //在Label对象的构造子中指定单元格位置(列，行，内容)   
		        
		        CellView cellView = new CellView();  			//单元格大小自适应
		        cellView.setAutosize(false); //设置自动大小
		        WritableCellFormat centerwcf = new WritableCellFormat();  			//内容居中
		        centerwcf.setAlignment(Alignment.CENTRE);//把水平对齐方式指定为居中 
		        centerwcf.setVerticalAlignment(VerticalAlignment.CENTRE);//把垂直对齐方式指定为居中 
		        	            
	            Label title = new Label(0, 0, "课程信息汇总_" + studentCountList.get(0).getCname()  + "_" + nowtime, centerwcf);
	            sheet.mergeCells(0, 0, studentCountList.size(), 0);		//合并单元格，从左上角第（0，0）格到右下角第（0，x）格子
	            sheet.addCell(title);
	            
	            sheet.addCell(new Label(0, 2, "总点名次数"));				           
	            sheet.addCell(new Label(0, 3, "到勤次数"));				        
	            sheet.addCell(new Label(0, 4, "其他情况"));				           
	            sheet.addCell(new Label(0, 5, "课堂表现情况"));			

		        for(int i = 0; i < studentCountList.size(); i++)
		        {
		       // 	sheet.setColumnView(i, cellView);
		        	Label lsname = new Label(i+1, 1, studentCountList.get(i).getSname());  
		            sheet.addCell(lsname);				            //将定义好的单元格添加到工作表中   
		            jxl.write.Number nallpointtime = new jxl.write.Number(i+1, 2, studentCountList.get(i).getAllpointtimes());  
		            sheet.addCell(nallpointtime);				    //插入数字    
		            jxl.write.Number ncometimes = new jxl.write.Number(i+1, 3, studentCountList.get(i).getCometimes());  
		            sheet.addCell(ncometimes);

	            	Label lothers = new Label(i+1, 4, studentCountList.get(i).getOthers());  
		            sheet.addCell(lothers);
		            Label lbehave = new Label(i+1, 5, studentCountList.get(i).getBehaves());  
		            sheet.addCell(lbehave);
		        }
		        workbook.write();  
	            workbook.close();  
	            if(file.exists())
				{
	            	String emailtitle = "轻松课堂信息统计-" + studentCountList.get(0).getCname();
	            	String content = "尊敬的轻松课堂用户，您好， \n" + "      附件内容为导出的信息统计，基础信息如下：\n"
	            			+ "      信息类型：单节课程-所有同学\n"
	            			+ "      统计课程：" + studentCountList.get(0).getCname() + "\n"
	            			+ "      统计时间：" + nowtime + "\n\n"
	            			+ "                               请勿回复此邮件";
	            	if(sendEmail(teacher.getTemail(), emailtitle, content, file))
	            	{
	            		map.put("result", "success");
	    	    		this.setExportJson(map);
	            	}
	            	else
	            	{
	            		map.put("result", "fail");
	        			this.setExportJson(map);
	            	}
	            	file.delete();
				}
	            else
	            {
	            	map.put("result", "fail");
        			this.setExportJson(map);
	            }
			}
			
		} catch (Exception e) {
			if(file.exists())
			{
				file.delete();
			}
			map.put("result", "fail");
			this.setExportJson(map);
			e.printStackTrace();
		}    
     
		return "exportfinish";
	}
	
	/**
	 * 发送单个学生所有课程汇总信息到指定邮箱
	 * @return exportfinish
	 */
	@SuppressWarnings({ "unchecked", "static-access" })
	public String adexportAllCourseInfo()
	{
		Map<String, Object> map = new HashMap<String, Object>();
		Teacher teacher = loginService.findTeacherById(tid);
		if(teacher != null)
		{
			if(teacher.getTemail().equals(""))
			{
				map.put("result", "noemail");
				this.setExportJson(map);
				return "exportfinish";
			}
		}
		String nowtime = new SimpleDateFormat("yyyy年MM月dd日 HH点mm分ss秒").format(new Date());		//格式化时间
		File file = null;

		try {
			JSONArray jsonArray = new JSONArray();
			//创建需要储存的list
			List<StudentCount> studentCountList = new ArrayList<>();
			if(!getStudentCountArray().isEmpty())
			{
				jsonArray = JSONArray.fromObject(getStudentCountArray());
				studentCountList = jsonArray.toList(jsonArray, new StudentCount(), new JsonConfig());
				file = new File("个人信息汇总_" + studentCountList.get(0).getSname()  + "_" + nowtime + ".xls");
				//将信息存入excel
		        WritableWorkbook workbook;
				workbook = Workbook.createWorkbook(file);
				//生成名为“信息汇总”的工作表，参数0表示这是第一页   
		        WritableSheet sheet = workbook.createSheet("信息汇总", 0); 

		        CellView cellView = new CellView();  			//单元格大小自适应
		        cellView.setAutosize(false); //设置自动大小
		        WritableCellFormat centerwcf = new WritableCellFormat();  			//内容居中
		        centerwcf.setAlignment(Alignment.CENTRE);//把水平对齐方式指定为居中 
		        centerwcf.setVerticalAlignment(VerticalAlignment.CENTRE);//把垂直对齐方式指定为居中 
		        	            
	            Label title = new Label(0, 0, "课程信息汇总_" + studentCountList.get(0).getCname()  + "_" + nowtime, centerwcf);
	            sheet.mergeCells(0, 0, studentCountList.size(), 0);		//合并单元格，从左上角第（0，0）格到右下角第（0，x）格子
	            sheet.addCell(title);
	            
	            sheet.addCell(new Label(0, 2, "总点名次数"));				           
	            sheet.addCell(new Label(0, 3, "到勤次数"));				        
	            sheet.addCell(new Label(0, 4, "其他情况"));				           
	            sheet.addCell(new Label(0, 5, "课堂表现情况"));				          
		        for(int i = 0; i < studentCountList.size(); i++)
		        {
		        	Label lsname = new Label(i+1, 1, studentCountList.get(i).getCname());  
		            sheet.addCell(lsname);				            //将定义好的单元格添加到工作表中   
		            jxl.write.Number nallpointtime = new jxl.write.Number(i+1, 2, studentCountList.get(i).getAllpointtimes());  
		            sheet.addCell(nallpointtime);				    //插入数字    
		            jxl.write.Number ncometimes = new jxl.write.Number(i+1, 3, studentCountList.get(i).getCometimes());  
		            sheet.addCell(ncometimes);
		            
	            	Label lothers = new Label(i+1, 4, studentCountList.get(i).getOthers());  
		            sheet.addCell(lothers);	
		           
		            Label lbehave = new Label(i+1, 5, studentCountList.get(i).getBehaves());  
		            sheet.addCell(lbehave);				       
		        }
	              
		        workbook.write();  
	            workbook.close();  
	            if(file.exists())
				{
	            	String emailtitle = "轻松课堂信息统计-" + studentCountList.get(0).getSname();
	            	String content = "尊敬的轻松课堂用户，您好， \n" + "      附件内容为导出的信息统计，基础信息如下：\n"
	            			+ "      信息类型：个人-所有课程\n"
	            			+ "      统计同学：" + studentCountList.get(0).getSname() + "\n"
	            			+ "      统计时间：" + nowtime + "\n\n"
	            			+ "                               请勿回复此邮件";
	            	if(sendEmail(teacher.getTemail(), emailtitle, content, file))
	            	{
	            		map.put("result", "success");
	    	    		this.setExportJson(map);
	            	}
	            	else
	            	{
	            		map.put("result", "fail");
	        			this.setExportJson(map);
	            	}
	            	file.delete();
				}
	            else
	            {
	            	map.put("result", "fail");
        			this.setExportJson(map);
	            }
			}
			
		} catch (Exception e) {
			if(file.exists())
			{
				file.delete();
			}
			map.put("result", "fail");
			this.setExportJson(map);
			e.printStackTrace();
		}    
     
		return "exportfinish";
	}
	
	/**
	 * 发送单个学生单节课程汇总信息到指定邮箱
	 * @return exportfinish
	 */
	public String adexportSingleInfo()
	{
		Map<String, Object> map = new HashMap<String, Object>();
		Teacher teacher = loginService.findTeacherById(tid);
		if(teacher != null)
		{
			if(teacher.getTemail().equals(""))
			{
				map.put("result", "noemail");
				this.setExportJson(map);
				return "exportfinish";
			}
		}
		String nowtime = new SimpleDateFormat("yyyy年MM月dd日 HH点mm分ss秒").format(new Date());		//格式化时间
		File file = null;
		try {
			if(!getStudentCountJson().isEmpty())
			{
	            JSONObject json = JSONObject.fromObject(getStudentCountJson());
	            String title = "单条信息汇总_" + json.getString("sname")  + "_" + json.getString("cname") + "_" + nowtime;
				//将信息存入excel
				file = new File(title + ".xls");
		        WritableWorkbook workbook;
				workbook = Workbook.createWorkbook(file);
				//生成名为“信息汇总”的工作表，参数0表示这是第一页   
		        WritableSheet sheet = workbook.createSheet("信息汇总", 0); 	
		        CellView cellView = new CellView();  			//单元格大小自适应
		        cellView.setAutosize(false); //设置自动大小
		        WritableCellFormat centerwcf = new WritableCellFormat();  			//内容居中
		        centerwcf.setAlignment(Alignment.CENTRE);//把水平对齐方式指定为居中 
		        centerwcf.setVerticalAlignment(VerticalAlignment.CENTRE);//把垂直对齐方式指定为居中 
		        	            
	            sheet.mergeCells(0, 0, 2, 0);		//合并单元格，从左上角第（0，0）格到右下角第（0，x）格子
	            sheet.addCell(new Label(0, 0, title));
		        	            	            
	        	Label tallpointtime = new Label(0, 1, "总点名次数");  		//第二行，总点名次数
	            sheet.addCell(tallpointtime);				
	            jxl.write.Number nallpointtime = new jxl.write.Number(1, 1, json.getInt("allpointtimes"));  
	            sheet.addCell(nallpointtime);				   
	            	            
	            Label tcometimes = new Label(0, 2, "到勤次数");  			//第三行，到勤次数
	            sheet.addCell(tcometimes);				    
	            jxl.write.Number ncometimes = new jxl.write.Number(1, 2, json.getInt("cometimes"));  
	            sheet.addCell(ncometimes);
	            
	            Label tothers = new Label(0, 3, "其他情况");  				//第四行开始，其他情况
	            sheet.addCell(tothers);	
	          
	            Label lothers = new Label(1, 3, json.getString("others"));  
	            sheet.addCell(lothers);	
	            
	            Label tbehave = new Label(0, 4, "课堂表现情况");  
	            sheet.addCell(tbehave);	
	  
	            Label lbehave = new Label(1, 4, json.getString("behaves"));  
	            sheet.addCell(lbehave);	
		            
	            workbook.write();  
	            workbook.close();  
	            if(file.exists())
				{
	            	String emailtitle = "轻松课堂信息统计-" + json.getString("sname") + "-" + json.getString("cname");
	            	String content = "尊敬的轻松课堂用户，您好， \n" + "      附件内容为导出的信息统计，基础信息如下：\n"
	            			+ "      信息类型：个人-单节课程\n"
	            			+ "      统计同学：" + json.getString("sname") + "\n"
	            			+ "      统计课程：" + json.getString("cname") + "\n"
	            			+ "      统计时间：" + nowtime + "\n\n"
	            			+ "                               请勿回复此邮件";
	            	if(sendEmail(teacher.getTemail(), emailtitle, content, file))
	            	{
	            		map.put("result", "success");
	    	    		this.setExportJson(map);
	            	}
	            	else
	            	{
	            		map.put("result", "fail");
	        			this.setExportJson(map);
	            	}
	            	file.delete();
				}
	            else
	            {
	            	map.put("result", "fail");
        			this.setExportJson(map);
	            }
			}
			
		} catch (Exception e) {
			if(file.exists())
			{
				file.delete();
			}
			map.put("result", "fail");
			this.setExportJson(map);
			e.printStackTrace();
		}    
        
		return "exportfinish";
	}
	
	
	//------------------------邮件发送--------------------------
    public boolean sendEmail(String to, String title, String content, File file) {

        Properties props = new Properties();
        String host = "smtp.163.com";
        // 设置发送邮件的邮件服务器的属性（这里使用网易的smtp服务器）
        props.put("mail.smtp.host", host);
        // 需要经过授权，也就是有户名和密码的校验，这样才能通过验证（一定要有这一条）
        props.put("mail.smtp.auth", "true");

        // 用刚刚设置好的props对象构建一个session
        Session session = Session.getDefaultInstance(props);

        // 有了这句便可以在发送邮件的过程中在console处显示过程信息，供调试使
        // 用（你可以在控制台（console)上看到发送邮件的过程）
        session.setDebug(false);

        // 用session为参数定义消息对象
        MimeMessage message = new MimeMessage(session);
        try {
            // 加载发件人地址
            message.setFrom(new InternetAddress("mikezhou77@163.com"));
            // 加载收件人地址
            message.addRecipient(Message.RecipientType.TO, new InternetAddress(to));
            // 加载标题
            message.setSubject(title);
            
            // 向multipart对象中添加邮件的各个部分内容，包括文本内容和附件
            Multipart multipart = new MimeMultipart();

            // 设置邮件的文本内容
            BodyPart contentPart = new MimeBodyPart();
            contentPart.setText(content);
            multipart.addBodyPart(contentPart);
            // 添加附件
            BodyPart messageBodyPart = new MimeBodyPart();
            DataSource source = new FileDataSource(file);
            // 添加附件的内容
            messageBodyPart.setDataHandler(new DataHandler(source));
            // 添加附件的标题
            // 这里很重要，通过下面的Base64编码的转换可以保证你的中文附件标题名在发送时不会变成乱码
         
           // sun.misc.BASE64Encoder enc = new sun.misc.BASE64Encoder();
            
            messageBodyPart.setFileName(MimeUtility.encodeWord(file.getName()));
            /*messageBodyPart.setFileName("=?GBK?B?"
                    + enc.encode(file.getName().getBytes()) + "?=");*/
            multipart.addBodyPart(messageBodyPart);

            // 将multipart对象放到message中
            message.setContent(multipart);
            // 保存邮件
            message.saveChanges();
            // 发送邮件
            Transport transport = session.getTransport("smtp");
            // 连接服务器的邮箱
            transport.connect(host, "mikezhou77@163.com", "Apple398");
            // 把邮件发送出去
            transport.sendMessage(message, message.getAllRecipients());
            transport.close();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }
}